﻿//-----------------------------------------------------------------------
// <copyright file="DataProvider.cs" company="NECSV">
//     Copyright (c) NECSV. All rights reserved.
// </copyright>
// <summary>Base class for all access to Oracel DB.</summary>
//-----------------------------------------------------------------------
namespace LMT.Db
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    
    /// <summary>
    /// Helper class for all access to Common database
    /// </summary>
    public abstract class DbProvider
    {
        /// <summary>
        /// value of database connection string
        /// </summary>
        protected string connection;

        /// <summary>
        /// detect whether environment has been initialized
        /// </summary>
        private bool isInitialize = false;

        /// <summary>
        /// Gets or sets the entity provider.
        /// </summary>
        /// <value>
        /// The entity provider.
        /// </value>
        internal protected EntityProvider EntityParser { get; set; }

        /// <summary>
        /// Initializes the oracle environment.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <exception cref="System.InvalidOperatorException"></exception>
        public virtual void InitializeEnvironment(string connection)
        {
            if (this.isInitialize)
            {
                throw new InvalidOperationException("Data Connection had been Initialized. Please close evironment before creating another.");
            }

            DbConnection enviCon = null;
            try
            {
                enviCon = this.CreateConnection(connection);
                enviCon.Open();

                // init instance of connection
                this.connection = connection;
                this.isInitialize = true;
            }
            finally
            {
                if (enviCon != null)
                {
                    enviCon.Dispose();
                }
            }
        }

        /// <summary>
        /// Determines whether [is valid connection] [the specified connection].
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="error">The error.</param>
        /// <returns>
        ///   <c>true</c> if [is valid connection] [the specified connection]; otherwise, <c>false</c>.
        /// </returns>
        public bool IsValidConnection(string connection, out string error)
        {
            DbConnection dataCon = null;
            error = string.Empty;
            try
            {
                if (connection.Contains("Pooling") | connection.Contains("POOLING"))
                {
                    string[] arrCon = connection.Split(new char[]{';'}, StringSplitOptions.RemoveEmptyEntries);
                    string conTmp = string.Empty;

                    for (int index = 0; index < arrCon.Length; index++)
                    {
                        conTmp = arrCon[index].Trim();
                        if (conTmp.StartsWith("Pooling", StringComparison.OrdinalIgnoreCase))
                        {
                            arrCon[index] = "Pooling = 'false'";
                        }
                    }

                    connection = string.Empty;
                    for (int index = 0; index < arrCon.Length; index++)
                    {
                        connection += arrCon[index] + ";";
                    }
                }
                else
                {
                    connection += ";Pooling = 'false'";
                }

                dataCon = this.CreateConnection(connection);
                dataCon.Open();
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
            finally
            {
                if (dataCon != null)
                {
                    dataCon.Dispose();
                }
            }
        }

        /// <summary>
        /// Closes the environment.
        /// </summary>
        public void CloseEnvironment()
        {
            this.connection = string.Empty;
            this.isInitialize = false;
        }

        /// <summary>
        /// Executes the sql query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns>number of rows affected.</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal int ExecuteNonQuery(string query)
        {
            return this.ExecuteNonQuery(query, CommandType.Text, null);
        }

        /// <summary>
        /// Executes the sql query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the command.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>number of rows affected.</returns>
        internal int ExecuteNonQuery(string query, CommandType comType)
        {
            return this.ExecuteNonQuery(query, comType, null);
        }

        /// <summary>
        /// Executes the sql query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>number of rows affected.</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal int ExecuteNonQuery(string query, DbParameter[] paras)
        {
            return this.ExecuteNonQuery(query, CommandType.Text, paras);
        }

        /// <summary>
        /// Executes the sql query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        /// <returns>number of rows affected.</returns>
        internal int ExecuteNonQuery(string query, CommandType comType, DbParameter[] paras)
        {
            this.CheckEnvironment();
            DbCommand oledbCom = null;

            try
            {
                // prepare parameter for executing pl/sql query
                oledbCom = this.PrepareExecute(query, comType, paras, false);
                return oledbCom.ExecuteNonQuery();
            }
            finally
            {
                this.CloseExecute(oledbCom, null, null, false);
            }
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="dataCom">The data Command.</param>
        /// <param name="query">The query.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>number of affected rows.</returns>
        internal int ExecuteNonQuery(DbCommand dataCom, DbParameter[] paras)
        {
            dataCom.Parameters.Clear();
            if (paras != null)
            {
                dataCom.Parameters.AddRange(paras);
            }

            return dataCom.ExecuteNonQuery();
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <returns>an implement instance of IEntity.</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T ExecuteReaderSingle<T>(string query) where T : IDbEntity
        {
            return this.ExecuteReaderSingle<T>(query, CommandType.Text, null);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>an implement instance of IEntity.</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T ExecuteReaderSingle<T>(string query, DbParameter[] paras) where T : IDbEntity
        {
            return this.ExecuteReaderSingle<T>(query, CommandType.Text, paras);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the command.</param>
        /// <returns>an implement instance of IEntity.</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T ExecuteReaderSingle<T>(string query, CommandType comType) where T : IDbEntity
        {
            return this.ExecuteReaderSingle<T>(query, comType, null);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>an implement instance of IEntity.</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T ExecuteReaderSingle<T>(string query, CommandType comType, DbParameter[] paras) where T : IDbEntity
        {
            this.CheckEnvironment();

            DbCommand oledbCom = null;
            DbDataReader oledbReader = null;
            T entity = default(T);

            try
            {
                // prepare parameter for executing pl/sql query
                oledbCom = this.PrepareExecute(query, comType, paras, false);

                oledbReader = oledbCom.ExecuteReader();
                if (oledbReader.HasRows)
                {
                    entity = this.EntityParser.CreateEntity<T>(oledbReader);
                }
                
                return entity;
            }
            finally
            {
                this.CloseExecute(oledbCom, null, oledbReader, false);
            }
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="oraCom">The ora COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>
        /// an implement instance of IEntity.
        /// </returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T ExecuteReaderSingle<T>(DbCommand oraCom, DbParameter[] paras) where T : IDbEntity
        {
            oraCom.Parameters.Clear();
            if (paras != null)
            {
                oraCom.Parameters.AddRange(paras);
            }

            DbDataReader oledbReader = oraCom.ExecuteReader();
            if (oledbReader.HasRows)
            {
                T entity = this.EntityParser.CreateEntity<T>(oledbReader);
                return entity;
            }

            return default(T);
        }

        /// <summary>
        /// Executes the reader single.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="oraCom">The ora COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns></returns>
        internal DbDataReader ExecuteReaderSingle(DbCommand oraCom, DbParameter[] paras)
        {
            oraCom.Parameters.Clear();
            if (paras != null)
            {
                oraCom.Parameters.AddRange(paras);
            }

            return oraCom.ExecuteReader();
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <returns>
        /// a list of implement instances of IEntity.
        /// </returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal List<T> ExecuteReaderList<T>(string query) where T : IDbEntity
        {
            return this.ExecuteReaderList<T>(query, CommandType.Text, null);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>
        /// a list of implement instances of IEntity.
        /// </returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal List<T> ExecuteReaderList<T>(string query, DbParameter[] paras) where T : IDbEntity
        {
            return this.ExecuteReaderList<T>(query, CommandType.Text, paras);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the command.</param>
        /// <returns>
        /// a list of implement instances of IEntity.
        /// </returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal List<T> ExecuteReaderList<T>(string query, CommandType comType) where T : IDbEntity
        {
            return this.ExecuteReaderList<T>(query, comType, null);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>
        /// a list of implement instances of IEntity.
        /// </returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal List<T> ExecuteReaderList<T>(string query, CommandType comType, DbParameter[] paras) where T : IDbEntity
        {
            this.CheckEnvironment();

            DbCommand oledbCom = null;
            DbDataReader oledbReader = null;
            List<T> lstEntity = null;

            try
            {
                // prepare parameter for executing pl/sql query
                oledbCom = this.PrepareExecute(query, comType, paras, false);

                oledbReader = oledbCom.ExecuteReader();
                if (oledbReader.HasRows)
                {
                    lstEntity = this.EntityParser.CreateEntityList<T>(oledbReader);
                }

                return lstEntity;
            }
            finally
            {
                this.CloseExecute(oledbCom, null, oledbReader, false);
            }
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">value of struct</typeparam>
        /// <param name="query">The query.</param>
        /// <returns>object value</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T? ExecuteScalar<T>(string query) where T : struct
        {
            return this.ExecuteScalar<T>(query, CommandType.Text, null);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">value of struct</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the command.</param>
        /// <returns>object value</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T? ExecuteScalar<T>(string query, CommandType comType) where T : struct
        {
            return this.ExecuteScalar<T>(query, comType, null);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">value of struct</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>object value</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T? ExecuteScalar<T>(string query, DbParameter[] paras) where T : struct
        {
            return this.ExecuteScalar<T>(query, CommandType.Text, paras);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">value of struct</typeparam>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>object value</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T? ExecuteScalar<T>(string query, CommandType comType, DbParameter[] paras) where T : struct
        {
            this.CheckEnvironment();
            DbCommand oledbCom = null;

            try
            {
                // prepare parameter for executing pl/sql query
                oledbCom = this.PrepareExecute(query, comType, paras, false);
                return oledbCom.ExecuteScalar() as T?;
            }
            finally
            {
                this.CloseExecute(oledbCom, null, null, false);
            }
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <typeparam name="T">value of struct</typeparam>
        /// <param name="dataCom">The data COM.</param>
        /// <param name="query">The query.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <returns>object value</returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal T? ExecuteScalar<T>(DbCommand dataCom, DbParameter[] paras) where T : struct
        {
            dataCom.Parameters.Clear();
            if (paras != null)
            {
                dataCom.Parameters.AddRange(paras);
            }

            return dataCom.ExecuteScalar() as T?;
        }

        /// <summary>
        /// Executes the object.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        internal object ExecuteObject(string query)
        {
            this.CheckEnvironment();
            DbCommand oledbCom = null;

            try
            {
                // prepare parameter for executing pl/sql query
                oledbCom = this.PrepareExecute(query, CommandType.Text, null, false);
                return oledbCom.ExecuteScalar();
            }
            finally
            {
                this.CloseExecute(oledbCom, null, null, false);
            }
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="dataCom">The data COM.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        internal object ExecuteScalar(DbCommand dataCom, DbParameter[] paras)
        {
            dataCom.Parameters.Clear();
            if (paras != null)
            {
                dataCom.Parameters.AddRange(paras);
            }

            return dataCom.ExecuteScalar();
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="table">The table.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal void FillTable(string query, DataTable table)
        {
            this.FillTable(query, CommandType.Text, null, table);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the command.</param>
        /// <param name="table">The table.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal void FillTable(string query, CommandType comType, DataTable table)
        {
            this.FillTable(query, comType, null, table);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <param name="table">The table.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal void FillTable(string query, DbParameter[] paras, DataTable table)
        {
            this.FillTable(query, CommandType.Text, paras, table);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <param name="table">The table.</param>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        internal void FillTable(string query, CommandType comType, DbParameter[] paras, DataTable table)
        {
            this.CheckEnvironment();
            DbDataAdapter oledbAdapter = null;
            DbCommand oledbCom = null;

            try
            {
                // prepare parameter for executing pl/sql query
                oledbCom = this.PrepareExecute(query, comType, paras, false);

                oledbAdapter = this.CreateDataAdapter(oledbCom);
                oledbAdapter.Fill(table);
            }
            finally
            {
                this.CloseExecute(oledbCom, oledbAdapter, null, false);
            }
        }

        /// <summary>
        /// Begins the batch execute.
        /// </summary>
        /// <param name="comType">Type of the COM.</param>
        /// <param name="useTransaction">if set to <c>true</c> [use transaction].</param>
        /// <returns>
        /// instance of DbCommand
        /// </returns>
        internal DbCommand BeginBatchExecute(CommandType comType, bool useTransaction)
        {
            this.CheckEnvironment();

            return this.PrepareExecute(string.Empty, comType, null, useTransaction);
        }

        /// <summary>
        /// Closes the batch execute.
        /// </summary>
        /// <param name="dataCom">The data command.</param>
        /// <param name="commitTransaction">if set to <c>true</c> [commit transaction].</param>
        internal void CloseBatchExecute(IDbCommand dataCom, bool commitTransaction)
        {
            this.CloseExecute((DbCommand)dataCom, null, null, commitTransaction);
        }

        /// <summary>
        /// Creates the connection.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        protected abstract DbConnection CreateConnection(string connection);

        /// <summary>
        /// Creates the data adapter.
        /// </summary>
        /// <param name="oledbCom">The oledb COM.</param>
        /// <returns></returns>
        protected abstract DbDataAdapter CreateDataAdapter(DbCommand oledbCom);

        /// <summary>
        /// Prepares the execute.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="comType">Type of the COM.</param>
        /// <param name="paras">The OLE db paras.</param>
        /// <param name="useTransaction">if set to <c>true</c> [use transaction].</param>
        /// <returns>
        /// instance of DbCommand with parameter and connection
        /// </returns>
        /// <exception cref="System.Data.OleDb.OleDbException"></exception>
        ///   
        /// <exception cref="System.InvalidOperatorException"></exception>
        private DbCommand PrepareExecute(string query, CommandType comType, DbParameter[] paras, bool useTransaction)
        {
            // create or get oledb connection from pool, it will return to pool after call CloseExecute()
            DbConnection oraCon = this.CreateConnection(this.connection);
            DbCommand oraCom = oraCon.CreateCommand();

            oraCom.CommandType = comType;

            if (!string.IsNullOrEmpty(query))
            {
                oraCom.CommandText = query;
            }

            if (paras != null)
            {
                oraCom.Parameters.AddRange(paras);
            }

            oraCon.Open();

            if (useTransaction)
            {
                oraCom.Transaction = oraCon.BeginTransaction();
            }

            return oraCom;
        }

        /// <summary>
        /// Closes the execute.
        /// </summary>
        /// <param name="oledbCom">The oledb command.</param>
        /// <param name="oledbAdapter">The oledb adapter.</param>
        /// <param name="oledbReader">The oledb reader.</param>
        /// <param name="commitTransaction">if set to <c>true</c> [commit transaction].</param>
        private void CloseExecute(DbCommand oledbCom, DbDataAdapter oledbAdapter, IDataReader oledbReader, bool commitTransaction)
        {
            if (oledbAdapter != null)
            {
                oledbAdapter.Dispose();
            }

            if (oledbReader != null)
            {
                oledbReader.Dispose();
            }

            if (oledbCom != null)
            {
                oledbCom.Parameters.Clear();

                if (oledbCom.Transaction != null)
                {
                    if (commitTransaction)
                    {
                        oledbCom.Transaction.Commit();
                    }
                    else
                    {
                        oledbCom.Transaction.Rollback();
                    }
                }

                DbConnection oledbCon = oledbCom.Connection;
                oledbCom.Dispose();
                oledbCon.Dispose();
            }
        }

        /// <summary>
        /// Checks the environment.
        /// </summary>
        /// <exception cref="System.InvalidOperationException"></exception>
        private void CheckEnvironment()
        {
            if (!this.isInitialize)
            {
                throw new InvalidOperationException("Data Connection is not initialize. Should InitializeEnvironment first.");
            }

            if (this.EntityParser == null)
            {
                throw new InvalidOperationException("Entity Provider is not initialize. It have to initialized first.");
            }
        }
    }
}